package com.zr.gdms.daoimpl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import com.zr.gdms.dao.CourseDaoZYH;
import com.zr.gdms.model.Application;
import com.zr.gdms.util.JDBCUtil;

import net.sf.json.JSON;
import net.sf.json.JSONArray;
import net.sf.json.JSONObject;
/**
 * 课题管理dao实现层
 * @author 张云辉
 *
 */
public class CourseDaoImplZYH implements CourseDaoZYH{

	@Override
	public boolean AddCourseByTime(String apptype, String appcontent,int uid,String appstate) {
		boolean bl = false;
		StringBuffer sql = new StringBuffer("");
		sql.append(" INSERT INTO application(apptype,appcontent,apptime,tid,appstate)");
		sql.append(" VALUES (?,?,?,?,?)");
		Connection con = JDBCUtil.getConnection();
		try {
			PreparedStatement pst = con.prepareStatement(sql.toString());
			pst.setString(1, apptype);
			pst.setString(2, appcontent);
			Date d = new Date();
			SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
			pst.setString(3, sdf.format(d));
			pst.setInt(4, uid);
			pst.setString(5, appstate);
			int i = pst.executeUpdate();
			if(i>0){
				bl=true;
			}
			JDBCUtil.closeJDBC(pst, con);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return bl;
	}

	@Override
	public JSONArray getAllCourseByTid(int tid) {
		JSONArray ja = new JSONArray();
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT course.cid,cname,cstate,tname,tdepart,sname");
		sql.append(" FROM course INNER JOIN teacher ON teacher.tid=course.tid");
		sql.append(" INNER JOIN score");
		sql.append(" ON score.cid = course.cid");
		sql.append(" INNER JOIN student");
		sql.append(" ON student.sid = score.sid");
		sql.append(" WHERE teacher.tid =?");
		Connection con = JDBCUtil.getConnection();
		PreparedStatement pst;
		try {
			pst = con.prepareStatement(sql.toString());
			pst.setInt(1, tid);
			ResultSet rs = pst.executeQuery();
			while(rs.next()){
				JSONObject j = new JSONObject();
				j.put("cid", rs.getInt("cid"));
				j.put("cname", rs.getString("cname"));
				j.put("cstate", rs.getString("cstate"));
				j.put("tname", rs.getString("tname"));
				j.put("tdepart", rs.getString("tdepart"));
				j.put("sname", rs.getString("sname"));
				ja.add(j);
			}
			JDBCUtil.closeJDBC(pst, con);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return ja;
	}

	@Override
	public List<Application> getCourseFromApByUid(int uid) {
		List<Application> as = new ArrayList<Application>();
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT *");
		sql.append(" FROM application");
		sql.append(" INNER JOIN teacher");
		sql.append(" ON teacher.tid = application.tid");
		sql.append(" WHERE teacher.uid =? AND application.apptype='上传课题'");
		Connection con = JDBCUtil.getConnection();
		PreparedStatement pst;
		try {
			pst = con.prepareStatement(sql.toString());
			pst.setInt(1, uid);
			ResultSet rs = pst.executeQuery();
			while(rs.next()){
				Application a = new Application();
				a.setApptype(rs.getString("apptype"));
				a.setAppid(rs.getInt("appid"));
				a.setAppcontent(rs.getString("appcontent"));
				a.setApptime(rs.getString("apptime"));
				a.setAppstate(rs.getString("appstate"));
				as.add(a);
			}
			JDBCUtil.closeJDBC(pst, con);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return as;
	}

	@Override
	public int getTidByUid(int uid) {
		int tid = 0;
		StringBuffer sql = new StringBuffer(" SELECT tid");
		sql.append(" FROM teacher");
		sql.append(" WHERE teacher.uid =?");
		Connection con = JDBCUtil.getConnection();
		try {
			PreparedStatement pst = con.prepareStatement(sql.toString());
			pst.setInt(1, uid);
			ResultSet r = pst.executeQuery();
			if(r.next()){
				tid = r.getInt("tid");
			}
			JDBCUtil.closeJDBC(pst, con);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return tid;
	}

	@Override
	public boolean delCourseByAppid(int appid) {
		boolean bl = false;
		StringBuffer sql = new StringBuffer(" DELETE FROM application");
		sql.append(" WHERE appid = ?");
		Connection con = JDBCUtil.getConnection();
		PreparedStatement pst;
		try {
			pst = con.prepareStatement(sql.toString());
			pst.setInt(1, appid);
			int i = pst.executeUpdate();
			if(i>0){
				bl=true;
			}
			JDBCUtil.closeJDBC(pst, con);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return bl;
	}

	@Override
	public boolean UpdateCourseByAppid(int appid,String appcontent) {
		boolean bl = false;
		StringBuffer sql = new StringBuffer("");
		sql.append(" UPDATE application");
		sql.append(" SET appcontent = ?");
		sql.append(" WHERE appid = ?");
		Connection con = JDBCUtil.getConnection();
		try {
			PreparedStatement pst = con.prepareStatement(sql.toString());
			pst.setString(1, appcontent);
			pst.setInt(2, appid);
			int i = pst.executeUpdate();
			if(i>0){
				bl = true;
			}
			JDBCUtil.closeJDBC(pst, con);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return bl;
	}

	@Override
	public boolean addScoreBySid(int sid, int score) {
		boolean bl = false;
		Connection con = JDBCUtil.getConnection();
		StringBuffer sql = new StringBuffer(" UPDATE score");
		sql.append(" SET score.score = ?");
		sql.append(" WHERE score.sid = ?");
		PreparedStatement pst;
		try {
			pst = con.prepareStatement(sql.toString());
			pst.setInt(1, score);
			pst.setInt(2, sid);
			int i = pst.executeUpdate();
			if(i>0){
				bl = true;
			}
			JDBCUtil.closeJDBC(pst, con);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return bl;
	}
	
	@Override
	public JSONArray getScoreByTid(int tid) {
		JSONArray ja = new JSONArray();
		StringBuffer sql = new StringBuffer(" SELECT cname,student.sid,course.cid,sname,sdepart,score");
		sql.append(" FROM score");
		sql.append(" INNER JOIN course");
		sql.append(" ON course.cid = score.cid");
		sql.append(" INNER JOIN student");
		sql.append(" ON student.sid = score.sid");
		sql.append(" WHERE course.tid = ?");
		Connection con = JDBCUtil.getConnection();
		try {
			PreparedStatement pst = con.prepareStatement(sql.toString());
			pst.setInt(1, tid);
			ResultSet rs = pst.executeQuery();
			while(rs.next()){
				JSONObject jo = new JSONObject();
				jo.put("cname", rs.getString("cname"));
				jo.put("cid", rs.getInt("cid"));
				jo.put("sname", rs.getString("sname"));
				jo.put("sdepart", rs.getString("sdepart"));
				jo.put("score", rs.getInt("score"));
				jo.put("sid", rs.getInt("sid"));
				ja.add(jo);
			}
			JDBCUtil.closeJDBC(pst, con);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return ja;
	}
}
